﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient; //for SqlConnection
using System.Data;      //for SqlDbType

using ModelLayer;

namespace DBLayer
{
    public class DBCity
    {
        private SqlConnection con;

        //constructor for DBCity, takes no parameters and brings no joy
        public DBCity()
        {
            con = DBConnection.getInstance().getDBCon();
        }

        /**
         * Adds a city to the database
         * */
        public void AddCityToDB(int postalCode, string cityName)
        {
            SqlParameter postalParam = new SqlParameter("@PostalCodeID", SqlDbType.Int, 4);      //@column name, type, max lenght
            postalParam.Value = postalCode;                                                      //set the value of the param to the method argument

            SqlParameter cityParam = new SqlParameter("@CityName", SqlDbType.VarChar, 50);       //same as above. TODO: Find solution to varying varchars!
            cityParam.Value = cityName;

            SqlCommand command = new SqlCommand();                                                //Instantiate myCommand
            command.Connection = con;                                                             //set myCommand to use the connection "con" 
            command.CommandText = "INSERT INTO City (PostalCodeID, CityName)" +                   //add commandtext 
                                  "VALUES (@PostalCodeID, @CityName)";

            command.Parameters.Add(cityParam);                                                    //adds the param to the command
            command.Parameters.Add(postalParam);

            try                                                                                   //All database calls must utilise Try/Catch
            {
                command.ExecuteNonQuery();                                                        //execute query
            }
            catch (SqlException e)
            {
                Console.WriteLine("FAIL: " + e);

            }
        }

        /**
         * Retrieves a city from the database based on postalCode
        */
        public City RetrieveCity(int postalCode)
        {
            City city = null;

            SqlDataReader reader = null;                                                        //instantiate reader

            SqlParameter postalParam = new SqlParameter("@PostalCodeID", SqlDbType.Int, 4);
            postalParam.Value = postalCode;

            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandText = "SELECT CityName, PostalCodeID FROM City WHERE PostalCodeId = @PostalCodeID";
            command.Parameters.Add(postalParam);

            try
            {
                reader = command.ExecuteReader();                                               //start the reader with the command object, as declared above


                while (reader.Read())                                                           //as long as there are results in reader, keep going
                {
                    city = new City(reader.GetString(0), reader.GetInt32(1));                   //add a new city object with the reader results. Order matters!

                }

                reader.Close();                                                                 //Not closing the reader kills a puppy...also the program
            }
            catch (SqlException e)
            {
                Console.WriteLine("FAIL: " + e);
            }


            return city;
        }

        /*
         * Retrieves all the cities (with postalcodes) in the database
         * and returns them in a List<City>
         * */
        public List<City> RetrieveAllTheCities()
        {
            List<City> cities = new List<City>();           //note how List and IList are compatible. fucked if I know why.

            SqlDataReader reader = null;

            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandText = "SELECT CityName, PostalCodeID FROM City";

            try
            {
                reader = command.ExecuteReader();


                while (reader.Read())
                {
                    cities.Add(new City(reader.GetString(0), reader.GetInt32(1)));
                }

                reader.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine("FAIL: " + e);            //Fail msg
            }


            return cities;
        }

        /**
         * test meffod. deletes the cities after the remaining tests has been run.
         * */
        public void DeleteCities()
        {
            SqlCommand command = new SqlCommand("DELETE FROM City", con);

            command.ExecuteNonQuery();
        }



        /**
         * Alternate way of writing SqlCommands!
         * This is slightly less elegant, and I like the other way better.
         * */
        //public void AddCityToDB(int postalCode, string cityName)
        //{
        //    SqlParameter postalParam = new SqlParameter("@PostalCodeID", SqlDbType.Int, 4);     //@column name, type, max lenght
        //    postalParam.Value = postalCode;

        //    SqlParameter cityParam = new SqlParameter("@CityName", SqlDbType.VarChar, 50);
        //    cityParam.Value = cityName;

        //    SqlCommand command = new SqlCommand("INSERT INTO City (PostalCodeID, CityName)" +       //column names
        //                                        "VALUES (@PostalCodeID, @CityName)", con);          //param names must be as specified above, add "con" as the connection to be used
        //    command.Parameters.Add(cityParam);                                                      //adds the param to the command
        //    command.Parameters.Add(postalParam);

        //    try
        //    {
        //        command.ExecuteNonQuery();                                                              //execute query
        //    }
        //    catch (SqlException e)
        //    {
        //        Console.WriteLine("FAIL: " + e);
        //    }
        //}

    }
}
